package BookManager;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//第八页，主要提供Book表中的增删改查方法
public class BookDao {
    //新增图书方法
    public void add(Book book){
        Connection connection=null;
        PreparedStatement statement=null;
        try {
            connection=DBUtil.getConnection();
            String sql="insert into book values(null,?,?,?,?,0)";
            statement=connection.prepareStatement(sql);
            statement.setString(1,book.getName());
            statement.setString(2,book.getAuthor());
            statement.setInt(3,book.getPrice());
            statement.setString(4,book.getType());
            int ret=statement.executeUpdate();
            if(ret!=1){
                System.out.println("新增书籍失败");
            }else{
                System.out.println("新增书籍成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,null);
        }

    }

    //查找图书方法
    public List<Book> findBook(){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        List<Book> list=new ArrayList<>();
        try {
            connection=DBUtil.getConnection();
            String sql="select * from book";
            statement=connection.prepareStatement(sql);
            resultSet=statement.executeQuery();
            while (resultSet.next()){
                Book book=new Book();
                book.setBookId(resultSet.getInt("bookId"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setPrice(resultSet.getInt("price"));
                book.setType(resultSet.getString("type"));
                book.setIsBorrowed(resultSet.getInt("isBorrowed")==1);
                list.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return list;
    }



    //按照书名查找图书方法
    public List<Book> findBookName(String name){
        Connection connection=null;
        ResultSet resultSet=null;
        PreparedStatement statement=null;
        List<Book> list=new ArrayList<>();
        try {
            connection=DBUtil.getConnection();
            String sql="select * from book where name=?";
            statement=connection.prepareStatement(sql);
            statement.setString(1,name);
            resultSet=statement.executeQuery();
            while (resultSet.next()){
                Book book=new Book();
                book.setBookId(resultSet.getInt("bookId"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setPrice(resultSet.getInt("price"));
                book.setType(resultSet.getString("type"));
                book.setIsBorrowed(resultSet.getInt("isBorrowed")==1);
                list.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return list;
    }



    //按照书籍Id删除图书
    public boolean delete(int bookId){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        try {
            connection=DBUtil.getConnection();
            String sql="delete from book where bookId=?";
            statement=connection.prepareStatement(sql);
            statement.setInt(1,bookId);
            int ret=statement.executeUpdate();
            if(ret!=1){
                return false;
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBUtil.close(connection,statement,resultSet);
        }
        return false;
    }



    //按照书籍ID借出书籍
    public boolean borrowBook(int bookId){
        Connection connection=null;
        PreparedStatement statement1=null;
        PreparedStatement statement2=null;
        ResultSet resultSet=null;
        try {
            connection=DBUtil.getConnection();
            String sql="select * from book where bookId=?";
            statement1=connection.prepareStatement(sql);
            statement1.setInt(1,bookId);
            resultSet=statement1.executeQuery();
            if(resultSet.next()){
                boolean isBorrowed=(resultSet.getInt("isBorrowed")==1);
                if(isBorrowed){
                    System.out.println("书已借出");
                    return false;
                }
            }else{
                return false;
            }
            sql="update book set isBorrowed=1 where bookId=?";
            statement2=connection.prepareStatement(sql);
            statement2.setInt(1,bookId);
            int ret=statement2.executeUpdate();
            if(ret!=1){
                System.out.println("借出失败");
                return false;
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement1==null){
                try {
                    statement1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement2==null){
                try {
                    statement2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }



    //按照书籍ID归还书籍
    public boolean returnBook(int bookId){
        Connection connection=null;
        PreparedStatement statement1=null;
        PreparedStatement statement2=null;
        ResultSet resultSet=null;
        try {
            connection=DBUtil.getConnection();
            String sql="select * from book where bookId=?";
            statement1=connection.prepareStatement(sql);
            statement1.setInt(1,bookId);
            resultSet=statement1.executeQuery();
            if(resultSet.next()){
                boolean isBorrowed=(resultSet.getInt("isBorrowed")==1);
                if(!isBorrowed){
                    System.out.println("书已被归还了");
                    return false;
                }
            }else{
                return false;
            }
            sql="update book set isBorrowed=0 where bookId=?";
            statement2=connection.prepareStatement(sql);
            statement2.setInt(1,bookId);
            int ret=statement2.executeUpdate();
            if(ret!=1){
                System.out.println("归还失败");
                return false;
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement1==null){
                try {
                    statement1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement2==null){
                try {
                    statement2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }

    public static void main(String[] args) {
//        BookDao bookDao=new BookDao();
//        Book book=new Book();
//        book.setName("红楼梦");
//        book.setAuthor("曹雪芹");
//        book.setPrice(6000);
//        book.setType("古典小说");
//        bookDao.add(book);


        BookDao bookDao=new BookDao();
//        List<Book> books=bookDao.findBook();
//        System.out.println(books);


//        List<Book> books=bookDao.findBookName("红楼梦");
//        System.out.println(books);

//        boolean bool=bookDao.delete(4);
//        System.out.println(bool);


    }
}
